package com.vsct.maia.auditl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.vsct.maia.auditl.dto.DatedExecTime;
import com.vsct.maia.auditl.dto.ErrorsTableLine;
import com.vsct.maia.auditl.dto.MethodTableLine;
import com.vsct.maia.auditl.dto.ServiceExecTimes;
import com.vsct.maia.auditl.dto.ServiceTableLine;

/**
 * Connect to DataBase and return data in POJO (Plain Old Java Object)
 * @author eric_karangwa
 *
 */
public class  DataManager {

		public  List<String>  getServices() {
			
			Connection connection=null;
			Statement statement=null;
			
			List <String> services= new ArrayList<String>();
			ResultSet resultSet = null;
			try {
				ConnexionBD conn = new ConnexionBD();				
				connection = conn.getConnection();
				statement = conn.getStatement();
				if(connection==null) {System.out.println("pas de connexion"); System.exit(1);}
				String query = "select distinct sla_service from t_auditoutline group by sla_service order by 1 ";
				resultSet = statement.executeQuery(query);
			
				while (resultSet.next()) 	
					services.add(resultSet.getString("sla_service"));
				connection.close();
				
			}  catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (null != resultSet)
						resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != statement)
						statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != connection)
						connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}		
			}
			return services;
		}	
		
		public  List<ServiceExecTimes> getExecTimeByCriteria(List<String> services, String dateDebutServ,String dateFinServ,String groupBy,String percentile){
			
			List<ServiceExecTimes>  finalRows= new ArrayList<ServiceExecTimes>();
			
			Connection connection=null;
			Statement statement=null;					
			ResultSet rs = null;			
			try {
				
				// connexion
				ConnexionBD conn = new ConnexionBD();				
				connection = conn.getConnection();
				statement = conn.getStatement();
				//date_trunc('hour', val) + date_part('minute', val)::int / 5 * interval '5 min'
				//calcul de nombre d'heures entre interval de temps donné e
				String qmin = " "; 
				if(groupBy.equals("minute")) {
					qmin = " + date_part('minute', sla_date)::int / 10 * interval '10 min'";
					groupBy="hour";
				}
					
				StringBuilder query = new StringBuilder();
				query.append("select  sla_service, date_trunc('"+groupBy+"',sla_date) "+qmin+" as sla_date_trunc, percentile_cont(array_agg(sla_exec_millis), "+percentile+" ) as sla_exec_millis_pctl ");
				query.append("from t_auditoutline where 1=1 ");
				if(services.size()>0)
				{
					if (!services.contains("ALL")) {
						query.append("and sla_service in (");
						boolean addComma = false;
						for (String service : services) {
							if (addComma)
								query.append(",");
							query.append(" '").append(service).append("' ");
							addComma = true;
						}
						query.append(")");
					}
				}
				query.append(" and sla_date between timestamp'"+dateDebutServ+"' and timestamp'"+dateFinServ+"'");
				query.append(" group by sla_service, sla_date_trunc order by 1,2");
				
				rs = statement.executeQuery(query.toString());
				
				String currentService = null;
				ArrayList<DatedExecTime> execTimes = new ArrayList<DatedExecTime> ();
				while(rs.next()) {
					String service = rs.getString("sla_service");
					if(service != null && !service.equals(currentService))
					{
						if(currentService != null) {
							finalRows.add(new ServiceExecTimes(currentService, execTimes));
						}
						currentService = service;
						execTimes = new ArrayList<DatedExecTime> ();
					}
					execTimes.add(new DatedExecTime(rs.getTimestamp("sla_date_trunc").getTime(), rs.getInt("sla_exec_millis_pctl")));
				}
				// Remaining data
				if(currentService != null)
				{
					finalRows.add(new ServiceExecTimes(currentService, execTimes));
				}
				
				connection.close();
				
			}  catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (null != rs)
						rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != statement)
						statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != connection)
						connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}		
			}
			return finalRows;
		}	
		

		public String getMinMaxDate(){
			Connection connec = null;
			Statement st = null;
			ResultSet rs = null;
			String minDate = null, maxDate = null, query=null;
			try{
				ConnexionBD conn = new ConnexionBD();
				connec = conn.getConnection();
				st = conn.getStatement();
				
				query = "select min(sla_date) as min,max(sla_date) as max from t_auditoutline";
				rs = st.executeQuery(query);
				if(rs.next()){
					minDate = rs.getString("min");
					maxDate = rs.getString("max");
				}		
				connec.close();;
			}  catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (null != rs)
						rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != st)
						st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != connec)
						connec.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}		
			}
				
			return minDate+"/"+maxDate;
		}
		
		public String getServTable(List<String> services, String dateDebutServ,String dateFinServ,String percentile){
			
			//TODO pour chaque service construire une ligne contenant les valeurs de ses parametres
			
			List<ServiceTableLine> table = new ArrayList<ServiceTableLine>();			
			ServiceTableLine tbLine;
			
			Connection connec = null;
			Statement st = null;
			ResultSet rs = null;
			StringBuilder query= new StringBuilder();
			try{
				
				ConnexionBD conn = new ConnexionBD();
				connec = conn.getConnection();
				st = conn.getStatement();
				
				query.append(" select sla_service as service,");
				query.append(" percentile_cont(array_agg(sla_exec_millis), "+percentile+" ) as percentile, ");
				query.append(" avg(sla_exec_millis) as average,  ");
				query.append(" count(*) as count_service, ");
				query.append(" count(*)/(EXTRACT(EPOCH FROM max(sla_date)-min(sla_date))/3600) as count_by_hour, ");
				
				query.append(" sum(case when sla_err_code != ' ' and sla_err_code != '0060' and sla_err_code != '0030' then 1 else 0 end)*100.0/count(*) as err_business, ");
				query.append(" sum(case when sla_err_code = '0060' or sla_err_code = '0030' then 1 else 0 end)*100.0/count(*) as err_tech ");
				query.append(" from t_auditoutline ");
				query.append(" where sla_date between timestamp'");
				query.append(" "+dateDebutServ+"' ");
				query.append(" and timestamp'");
				query.append(" "+dateFinServ+"'");		
								
					if ( services.size()==0 || services.contains("ALL")) {
						
						services.clear();
						services  =this.getServices(); }
						query.append("and sla_service in (");
						boolean addComma = false;
						for (String service : services) {
							if (addComma)
								query.append(",");
								query.append(" '").append(service).append("' ");
							
							addComma = true;
						}
				query.append(" ) ");
				query.append(" group by service order by 1");
				
				rs = st.executeQuery(query.toString());
				while(rs.next()){
					tbLine = new ServiceTableLine();
					tbLine.setService(rs.getString("service"));
					tbLine.setPerCentille(rs.getDouble("percentile"));
					tbLine.setMoyenneTempsExec(rs.getDouble("average"));
					tbLine.setSumTpsExec(rs.getLong("count_service"));
					tbLine.setPctNbAppelPerHour(rs.getDouble("count_by_hour"));
					tbLine.setPctErreurClass(rs.getDouble("err_business"));
					tbLine.setPctErreurTechn(rs.getDouble("err_tech"));	
					table.add(tbLine);
					//table.
				}
				//System.out.println(query);
				connec.close();
			}  catch (SQLException e) {
				e.printStackTrace();
			} finally {
				
				try {
					if (null != rs)
						rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != st)
						st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != connec)
						connec.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}		
			}			
			TableBuilderJson tb = new TableBuilderJson();
			String tbStr = tb.getServiceTableJson(table);
			return tbStr;
		}
		
public String getMethodTable(List<String> services, String dateDebutServ,String dateFinServ,String percentile){
			
			//TODO pour chaque service construire une ligne contenant les valeurs de ses parametres
		
			List<MethodTableLine> table = new ArrayList<MethodTableLine>();			
			MethodTableLine tbLine;
		
			Connection connec = null;
			Statement st = null;
			ResultSet rs = null;
			StringBuilder query= new StringBuilder();
			try{
				
				ConnexionBD conn = new ConnexionBD();
				connec = conn.getConnection();
				st = conn.getStatement();
				
				query.append(" select sla_service as service, ");
				query.append(" sla_method as method,");
				query.append(" percentile_cont(array_agg(sla_exec_millis), "+percentile+" ) as percentile, ");
				query.append(" avg(sla_exec_millis) as average,  ");
				query.append(" count(*) as count_service, ");
				query.append(" count(*)/(EXTRACT(EPOCH FROM max(sla_date)-min(sla_date))/3600) as count_by_hour, ");
				
				query.append(" sum(case when sla_err_code != ' ' and sla_err_code != '0060' and sla_err_code != '0030' then 1 else 0 end)*100.0/count(*) as err_business, ");
				query.append(" sum(case when sla_err_code = '0060' or sla_err_code = '0030' then 1 else 0 end)*100.0/count(*) as err_tech ");
				query.append(" from t_auditoutline ");
				query.append(" where sla_date between timestamp'");
				query.append(" "+dateDebutServ+"' ");
				query.append(" and timestamp'");
				query.append(" "+dateFinServ+"'");		
				
					if ( services.size()==0 || services.contains("ALL")) {
						services.clear();
						services  =this.getServices(); }
						query.append("and sla_service in (");
						boolean addComma = false;
						for (String service : services) {
							if (addComma)
								query.append(",");
								query.append(" '").append(service).append("' ");
							
							addComma = true;
						}
				query.append(" ) ");
				query.append(" group by service,method order by 1,2");
				System.out.println(query);
				rs = st.executeQuery(query.toString());
				while(rs.next()){
					tbLine = new MethodTableLine();
					tbLine.setService(rs.getString("service"));
					tbLine.setMethode(rs.getString("method"));
					tbLine.setPerCentille(rs.getDouble("percentile"));
					tbLine.setMoyenneTempsExec(rs.getDouble("average"));
					tbLine.setSumTpsExec(rs.getLong("count_service"));
					tbLine.setPctNbAppelPerHour(rs.getDouble("count_by_hour"));
					tbLine.setPctErreurClass(rs.getDouble("err_business"));
					tbLine.setPctErreurTechn(rs.getDouble("err_tech"));	
					table.add(tbLine);
					//table.
				}
			
				connec.close();
			}  catch (SQLException e) {
				e.printStackTrace();
			} finally {
				
				try {
					if (null != rs)
						rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != st)
						st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				try {
					if (null != connec)
						connec.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}		
			}			
			TableBuilderJson tb = new TableBuilderJson();
			String tbStr = tb.getMethodTableJson(table);
			return tbStr;
		}

public String getErrorsTable(List<String> services, String dateDebutServ,String dateFinServ){
	
	//TODO pour chaque service construire une ligne contenant les valeurs de ses parametres
	
	List<ErrorsTableLine> table = new ArrayList<ErrorsTableLine>();			
	ErrorsTableLine tbLine;

	Connection connec = null;
	Statement st = null;
	ResultSet rs = null;
	StringBuilder query= new StringBuilder();
	try{
		
		ConnexionBD conn = new ConnexionBD();
		connec = conn.getConnection();
		st = conn.getStatement();
		
		query.append(" select b.sla_service as service, ");
		query.append(" b.sla_method as method,");
		query.append(" b.sla_err_code as erreurCode, ");
		query.append(" count(b.*) * 100.0 /  ");
		query.append(" (select count(a.*) ");
		query.append(" from t_auditoutline a  ");
		query.append(" where a.sla_service=b.sla_service and a.sla_method=b.sla_method) as average, ");
		query.append(" b.sla_err_msg as libelleErreur ");
		query.append(" from  t_auditoutline b ");
		query.append(" where b.sla_date between timestamp'");
		query.append(" "+dateDebutServ+"' ");
		query.append(" and timestamp'");
		query.append(" "+dateFinServ+"'");		
		query.append(" and b.sla_err_code <> ' ' ");	
		
			if ( services.size()==0 || services.contains("ALL")) {
				services.clear();
				services  =this.getServices(); }
				query.append(" and b.sla_service in (");
				boolean addComma = false;
				for (String service : services) {
					if (addComma)
						query.append(",");
						query.append("'").append(service).append("'");
					
					addComma = true;
				}
		query.append(" ) ");
		query.append(" group by service, method, erreurCode, libelleErreur order by 1,2,3");
		
		rs = st.executeQuery(query.toString());
		while(rs.next()){
			tbLine = new ErrorsTableLine();
			tbLine.setService(rs.getString("service"));
			tbLine.setMethode(rs.getString("method"));
			tbLine.setErreurCode(rs.getString("erreurCode"));
			tbLine.setPourcentage(rs.getDouble("average"));
			tbLine.setLibelleErreur(rs.getString("libelleErreur"));
			table.add(tbLine);			
		}	
		connec.close();
	}  catch (SQLException e) {
		e.printStackTrace();
	} finally {
		
		try {
			if (null != rs)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			if (null != st)
				st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			if (null != connec)
				connec.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}			
	TableBuilderJson tb = new TableBuilderJson();
	String tbStr = tb.getErrorsTableJson(table);
	if(tbStr==null){
		System.out.println("DataManager.getErrorsTable: "
				+ "tbStr is null");
		return null;
		}
	return tbStr;
}

}
